package com.wanghua.spring.test;

import com.wanghua.spring.util.JavaProperties;
import org.hibernate.engine.jdbc.BinaryStream;
import org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl;
import org.jaxen.Context;

import javax.naming.InitialContext;
import java.beans.*;
import java.io.*;
import java.sql.*;
import java.sql.Date;
import java.sql.Statement;
import java.util.*;

/*
* 使用JDBC访问MySql需要使用MySql驱动 mysql-connector-java.jar。
* */
public class JdbcDemo {
    public static void main(String[] args) throws Exception {
        String userDir = System.getProperty("user.dir"); //D:\技术区\Java\Spring\Spring
        String jdbcProps = userDir + "/src/jdbc.properties";
        String connectionString = JavaProperties.getValue(jdbcProps, "c3p0.url");

        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            connection = DriverManager.getConnection(connectionString, "root", "solution");
            //statement = connection.createStatement();
            //Statement 默认返回的 ResultSet 只可往后滚动，因此只有 next()、last() 方法是可用的。要想使用 previous()、first() 方法，需要用下面带参方式创建。
            statement = connection.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE
            );

            //新增（Statement）
            String insertSql = "insert into tb_mail(mail_from,mimeMessage,sent,subject,mail_to) values ('chada_1@163.com', 0, 1, 'jdbc插入', '385277162@qq.com');";
            int result = statement.executeUpdate(insertSql, Statement.RETURN_GENERATED_KEYS); //返回受影响的行数，使用第二个参数后，可以使用 statement.getGeneratedKeys() 返回MySql自动生成的ID
            System.out.println(result + " 条记录被添加到数据库中。");
            rs = statement.getGeneratedKeys();  //获取自动生成的键值。
            while (rs.next()) {
                System.out.println("它的自增ID为：" + rs.getInt(1));   //ResultSet 每条记录的列，从1开始的。
            }
            rs.previous();
            int insertId = rs.getInt(1);
            rs.close();

            //修改（Statement）
            String updateSql = "update tb_mail set subject=concat(subject,'(已更新)') where id=" + insertId;
            result = statement.executeUpdate(updateSql); //返回受影响的行数
            System.out.println("ID为 " + insertId + " 的记录被修改。");

            //删除（Statement）
            String deleteSql = "delete from tb_mail where id=" + insertId;
            result = statement.executeUpdate(deleteSql); //返回受影响的行数
            System.out.println("ID为 " + insertId + " 的记录被删除。");

            //新增（PreparedStatement）
            //PreparedStatement 接口继承自 Statement 接口，因此拥有上面 Statement 上的方法。
            //PreparedStatement 接口可以使用参数，它允许使用不完整的SQL语句，空缺的部门使用问号(?)替代，直到执行前的时候设置进去。
            //PreparedStatement 中不变的部分会预编译以提高性能。流行的框架如 Hibernate、EJB3都倾向于使用 PreparedStatement。
            String insertSql2 = "insert into tb_mail(mail_from,mimeMessage,sent,subject,mail_to,mail_picture) values ('chada_1@163.com', 0, ?, 'PreparedStatement 插入',?,?);";
            PreparedStatement preparedStatement = connection.prepareStatement(insertSql2);  //预编译
            preparedStatement.setInt(1, 1);  //设置第一个参数（从1开始的）
            preparedStatement.setString(2, "385277162@qq.com");  //设置第二个参数
            File file = new File(jdbcProps);
            FileInputStream stream = new FileInputStream(file);
            //BLOB用于存储二进制数据，Text存储大文本，他们的长度都不能大于 65536 字节。如果数过长，可以使用 LONGBLOB、LONGTEXT。
            preparedStatement.setBinaryStream(3, stream, file.length());  //存储二进制数据，MySql用BLOB类型字段。这个file文件其实并不是二进制的，我们就当成二进制存储把。
            //System.currentTimeMillis();
            //preparedStatement.setDate(2, new java.sql.Date((new java.util.Date()).getTime()));
            result = preparedStatement.executeUpdate();
            //下面是读取二进制数据
            String selectSql2 = "select * from tb_mail where id=7";
            preparedStatement = connection.prepareStatement(selectSql2);  //预编译
            rs = preparedStatement.executeQuery();
            rs.next();
            InputStream ins = rs.getBinaryStream("mail_picture");
            if (ins != null) {
                byte[] b = new byte[1024];
                int len = 0;
                while ((len = ins.read(b)) != -1)
                    System.out.write(b, 0, len);
            }

            //批处理（Statement）
            //如果数据比较多，比如4万多条，不能一次批处理这4万条了，需要分批次，比如100条执行一次批处理。
            //能够执行批处理的SQL必须是 insert、update、delete等返回 int 类型的SQL。
            for (int i = 0; i < 3; i++) {
                statement.addBatch(insertSql);
            }
            int[] resultArray = statement.executeBatch();  //批量执行将每句SQL执行结果组成 int[] 数组。
            System.out.print("Statement批处理，受影响的行数分别是：");
            for (int i = 0; i < resultArray.length; i++) {
                System.out.print(resultArray[i] + ", ");
            }
            System.out.println();

            //批处理（PreparedStatement）
            String insertSql3 = "insert into tb_mail(mail_from,mimeMessage,sent,subject,mail_to) values ('chada_1@163.com', 0, ?, 'PreparedStatement 插入',?);";
            preparedStatement = connection.prepareStatement(insertSql3);
            for (int i = 0; i < 3; i++) {
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2, "385277162@qq.com");
                preparedStatement.addBatch();
            }
            resultArray = preparedStatement.executeBatch();
            System.out.print("PreparedStatement批处理，受影响的行数分别是：");
            for (int i = 0; i < resultArray.length; i++) {
                System.out.print(resultArray[i] + ", ");
            }
            System.out.println();

            //处理结果集
            //1) 查询多个结果集
            rs = statement.executeQuery("select * from tb_mail");
            while (rs.next()) {
                String mailFrom = rs.getString("mail_from");
                String mailTo = rs.getString("mail_to");
            }
            //进行第一次查询时，statement 会返回一个 ResultSet 对象。进行第二次查询时， statement 会返回一个全新的对象。中间不需要执行 rs.close() 关闭第一个当对象。
            //这是因为 Statement 与 PreparedStatement 有一个特性，在返回 ResultSet 结果集时，会自动关闭上一次查询的结果集。
            rs = statement.executeQuery("select * from tb_mail_attachment");
            while (rs.next()) {
                String filename = rs.getString("filename");
                String filepath = rs.getString("filepath");
            }
            rs.beforeFirst(); //如果用 rs.first()，那么 rs.next() 后是第二条记录了。

            //2) ResultSetMetaData
            //前面的查询都是预先知道列的名字，其实 ResultSet 对象的列名可由 ResultSetMetaData 元数据获得。遍历元数据即可获知 ResultSet 中有哪些列，例如列名称、列类型、列长度等。
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();
            String[] columns = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                columns[i] = meta.getColumnName(i + 1);  //获取列名，从1开始的。
            }
            System.out.println("-----------------------");
            while (rs.next()) {
                for (String column : columns) {
                    String val = rs.getString(column);
                    System.out.print(column + "=" + val + ", ");
                }
                System.out.println();
            }
            System.out.println("-----------------------");

            //事物
            Connection conn = null;
            Statement stmt = null;
            try {
                DriverManager.registerDriver(new com.mysql.jdbc.Driver());
                conn = DriverManager.getConnection(connectionString, "root", "solution");
                conn.setAutoCommit(false);  //将自动提交设为false。如果用"SHOW VARIABLES LIKE '%AUTOCOMMIT%';"查看此变量还是 ON 的。
                stmt = conn.createStatement();

                int result1 = stmt.executeUpdate("update tb_mail set subject='123' where id=12");
                int result2 = stmt.executeUpdate("update tb_mail set subject='456' where id=13");
                if (result1 == 1 && result2 == 1) {
                    conn.commit();
                } else {
                    conn.rollback();
                    //throw new Exception("数据更新失败。");  //事物回滚不仅可以用 rollback()，还可以抛异常。如果程序没有执行 commit()，最后也事物会回滚的。
                }
            } finally {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            }

            //数据源（连接池）
            /*
                做测试的话你只能在web上面做测试，因为连接池是部署到tomcat服务器上，不能用application做测试的。只要把下面代码考到Action测试下就好了。
                高并发时创建、断开Connection会消耗一定时间、资源。此时需要避免频繁创建、断开数据库连接。工程师们提出了数据源技术，也称为连接池
                数据源一般实现自 javax.sqlDataSource 接口。Tomcat 中也内置了数据源支持。使用时需要按 Tomcat 文档配置即可，例如：
                <Context cookie="true">
                    auth="Container"
                    type="javax.sql.DataSource"
                    maxActive="100" maxIdle="30" maxWait="10000"
                    username="root" password="solution"
                    driverClassName="com.mysql.jdbc.Driver"
                    url="jdbc:mysql://localhost:3306/spring?characterEncoding=utf8&amp;useUnicode=true&amp;mysqlEncoding=utf8&amp;zeroDateTimeBehavior=round" />
                </Context>
                上面的配置在 apache-tomcat-8.0.14/conf/context.xml 中，也可以配置在 apache-tomcat-8.0.14/conf/server.xml。
                但要注意：tomcat.jar 包要放在 apache-tomcat-8.0.14/lib 文件夹中，而不能放在Web程序中。然后要在Web程序的 web.xml 中配置数据源引用：
                <resource-ref>
                    <description>DB Connection</description>
                    <res-ref-name>jdbc/databaseWeb</res-ref-name>
                    <res-type>javax.sql.DataSource</res-type>
                    <res-auth>Container</res-auth>
                </resource-ref>
                数据源的名称为 "jdbc/databaseWeb"。这是一个JNDI资源，Java程序中可以通过查找该 JNDI 资源获取该数据源。下面使用Java代码查找 JNDI：
            */
            try {
                javax.naming.Context initContext = new javax.naming.InitialContext();
                javax.naming.Context envContext = (javax.naming.Context) initContext.lookup("java:/comp/env");  //获取所有的资源。做测试的话你只能在web上面做测试，因为连接池是部署到tomcat服务器上，不能用application做测试的。否则这里报错。
                javax.sql.DataSource ds = (javax.sql.DataSource) envContext.lookup("jdbc/databaseWeb"); //获取名称为 "jdbc/databaseWeb" 的JNDI资源
                Connection conn2 = ds.getConnection();
                Statement stmt2 = conn2.createStatement();
                ResultSet rs2 = stmt2.executeQuery("select * from tb_mail");
                rs2.close();
                stmt2.close();
                conn2.close(); //这里实际是释放连接到数据源（连接池）中，而不是关闭。
            } catch (Throwable e) {
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null)
                statement.close();
            if (connection != null)
                connection.close();
            if (rs != null)
                rs.close();
        }
    }
}
